create trigger home_recommendation1
    on history_order_have
    after update
    as
begin
    DECLARE @time_number varchar(20);--历史订单号
    DECLARE @oldtime_number date;--七天前订单时间
    DECLARE @oldtime_time date;--订单完成时间
    --获取订单号已经七天前的订单号
    select @time_number=history_order_number from inserted;
    select @oldtime_time=finish_time
    from history_order
    where  history_order_number=@time_number

    select dateadd(day,2,'2004-10-15')

    select @oldtime_number=dateadd(day,-7,@oldtime_time)
--删除原先存在的推荐菜品标识
    update dish
    set is_recommend =0;
    --0是不推荐，1是推荐
    --提供新的推荐菜品
    update dish
    set is_recommend = 1
    where dish_number in (
        -- 菜品评分
        select top 20  dish_number
        from history_order_have
             -- 获取一个月内的订单总额
        WHERE history_order_number in (
            select history_order_number
            from   history_order
            where finish_time
                      between	@oldtime_number
                      and @oldtime_time
        )
        group by  dish_number
        order by avg(order_dish_grade)
    )
end
go

